﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using MYTech.Utils;

namespace MYTech.Data
{
    /// <summary>
    /// Office相关
    /// </summary>
    public class Office
    {
        /// <summary>
        /// 导入xls表格到内存
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static List<System.Data.DataTable> LoadXlsData(string filePath)
        {
            return LoadXlsData(filePath, null);
        }

        /// <summary>
        /// 导入xls表格到内存
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public static List<System.Data.DataTable> LoadXlsData(string filePath, ReportProgressStatus report)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                object missing = System.Reflection.Missing.Value;
                excel.Application.Workbooks.Open(filePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                Workbooks books = excel.Workbooks;
                List<System.Data.DataTable> dts = new List<System.Data.DataTable>();
                foreach (Workbook book in books)
                {
                    for (int sheetIndex = 1; sheetIndex <= book.Worksheets.Count; sheetIndex++)
                    {
                        Worksheet sheet = (Worksheet)book.Worksheets[sheetIndex];
                        System.Data.DataTable result = new System.Data.DataTable(sheet.Name);
                        if (report != null)
                            report(string.Format("开始导入\"{0}\"数据...", sheet.Name));
                        if (sheet.UsedRange.Rows.Count < 2)
                            continue;
                        else
                        {
                            for (int k = 1; k < sheet.Columns.Count; k++)
                            {
                                Range r = (Range)sheet.Cells[1, k];
                                string name = r.Text.ToString();
                                if (/*!name.Contains("Column") &&*/ !string.IsNullOrEmpty(name))
                                    result.Columns.Add(name);
                            }
                        }
                        int columnsCount = result.Columns.Count;
                        string cellPos = string.Format("{0}{1}", Convert.ToChar('A' + columnsCount - 1), sheet.UsedRange.Rows.Count - 1);
                        object[] items = new object[columnsCount];
                        int index = 0;
                        foreach (Range range in sheet.get_Range("A2", cellPos))
                        {
                            string val = range.Cells.Value2 != null ? range.Cells.Value2.ToString() : null;
                            items[index % columnsCount] = val;
                            index++;
                            if (index % columnsCount == 0)
                            {
                                result.Rows.Add(items);
                                items = new object[columnsCount];
                            }
                        }
                        dts.Add(result);
                        if (report != null)
                            report(string.Format("\"{0}\"数据导入完成，共导入{1}条数据...", sheet.Name, result.Rows.Count));
                    }
                }
                return dts;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
